library(tidyverse)
library(data.table)
library(here)
library(sassy)
library(RPostgres)
setwd(dirname(rstudioapi::getActiveDocumentContext()$path)); 
setwd("../../")

# Open the log
lf <- log_open("output/log/log_A_CCMLinkingTable.log", autolog = T, show_notes = F)


# Send code to the log
log_code()

# 1 Read data from WRDS -------------------------------------------------------------------
sep("1 Read data from WRDS")


wrds <- dbConnect(Postgres(),
                  host='wrds-pgdata.wharton.upenn.edu',
                  port=9737,
                  dbname='wrds',
                  sslmode='require',
                  user='...').  # Replace with your user name

SQL_statement <- 
  "
  SELECT a.gvkey, a.cik, a.sic, a.naics, b.linkprim, 
          b.linktype, b.liid, b.lpermno as permno, b.lpermco as permco,
          b.linkdt as timeLinkStart_d, b.linkenddt as timeLinkEnd_d
  FROM comp.names as a
  INNER JOIN crsp.ccmxpf_lnkhist as b
    ON a.gvkey = b.gvkey
  WHERE b.linktype in ('LC', 'LU')
  AND b.linkprim in ('P', 'C')
  ORDER BY a.gvkey
"

res <- dbSendQuery(conn = wrds, statement = SQL_statement)
df_CCMLinkingTable <- dbFetch(res)
dbClearResult(res)

# save
fwrite(df_CCMLinkingTable, here("data", "Link", "CCMLinkingTable.csv"))
saveRDS(df_CCMLinkingTable, here("data", "Link", "CCMLinkingTable.RDS"))


# Close log
log_close()

# View results
writeLines(readLines(lf))
